package com.dean.service;

import cn.hutool.core.util.StrUtil;
import com.dean.constants.DeanUiConstants;
import com.dean.dao.DeanToolDao;
import com.dean.entity.DeanImportInfo;
import com.dean.entity.SysDbConnInfo;
import com.dean.exception.DeanRuntimeException;
import com.dean.utils.DeanJdbcUtils;
import com.dean.utils.DeanSqlUtils;
import com.dean.utils.GenCsvUtils;
import org.apache.commons.csv.CSVFormat;
import org.apache.commons.csv.CSVParser;
import org.apache.commons.csv.CSVPrinter;
import org.apache.commons.csv.QuoteMode;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.nio.charset.Charset;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.*;
import java.time.LocalDateTime;
import java.util.*;

/**
 * apache csv 数据操作
 */
public class ApacheCsvOprService {
    Logger logger = LoggerFactory.getLogger(ApacheCsvOprService.class);

    // 默认单次提交数据量为 1000;
    private static final Integer defaulCommitNum = 1000;

    private static final Integer defaultFetchSize = 1000;

    private Connection  connection;

    private PreparedStatement ps;

    private ApacheCsvOprService(){
    }
    private static volatile ApacheCsvOprService INSTANCE;
    public static ApacheCsvOprService getInstance(){
        if(INSTANCE == null){
            synchronized (ApacheCsvOprService.class){
                if(INSTANCE == null){
                    INSTANCE = new ApacheCsvOprService();
                }
            }
        }
        return INSTANCE;
    }


    /**
     * csv导入
     * @param connectInfo
     * @param fileNames
     * @param importInfo
     */
    public void importCsv(SysDbConnInfo connectInfo, List<File> fileNames, DeanImportInfo importInfo) {
        for (File file : fileNames) {
            String fileName = file.getPath();
            importCsv(connectInfo,fileName,importInfo);
        }
    }

    /**
     * 导入csv
     * @param connectInfo
     * @param fileName
     * @param importInfo
     */
    private void importCsv(SysDbConnInfo connectInfo, String fileName, DeanImportInfo importInfo) {
        // 校验文件名称
        if(StrUtil.isBlank(fileName)){
            DeanRuntimeException.throwException(2001,"校验错误,参数[fileName]不能为空");
        }
        String tableName = importInfo.getTableName();
        String [] head = importInfo.getFields();
        String sql = crtSql(tableName,head);
        String delimiter = importInfo.getDelimiter();
        Character quote = importInfo.getQuote();
        Boolean trim = importInfo.getTrim();
        String charset = importInfo.getCharSet();
        Integer skipHead = importInfo.getSkipHead();
        Integer skipTail = importInfo.getSkipTail();
        Integer commitNum = importInfo.getCommitNums() == null?defaulCommitNum:importInfo.getCommitNums();

        // 组装数据 并保存到数据库
        List<List<String>> data = new ArrayList<List<String>>();

        // 读取文件行数
        long count = -1;
        try {
            count =  Files.lines(Paths.get(fileName)).count();
            logger.info("读取到文件{},记录数为{}",fileName,count);
            // 检查是否需要进行文件导入 文件记录数小于或等于跳过的记录数 则不进行处理
            if(count <= (skipHead+skipTail)){
                logger.warn("文件:{},记录数为:{},跳过前{}行,跳过后{}行,需要导入的数量为:{},注意配置及文件是否正确",fileName,count,skipHead,skipTail,count - skipHead - skipTail);
                return;
            }
        } catch (IOException e) {
            logger.error(e.getMessage(),e);
            throw DeanRuntimeException.throwException(996, e.getMessage());
        }
        // 不跳过标题行 自己根据实际情况丢弃行
        CSVFormat csvFormat = CSVFormat.RFC4180.builder().setQuoteMode(QuoteMode.MINIMAL).setTrim(trim).
                setSkipHeaderRecord(false).setQuote(quote).setDelimiter(delimiter).build();

        try(CSVParser parser = csvFormat.parse(new InputStreamReader(new FileInputStream(fileName),charset))){
            long finalCount = count;
            parser.stream().forEach(
                    row->{
                        // logger.debug("{}",row);
                        data.add(row.toList());
                        if(data.size() == commitNum){
                            saveDb(sql,data,connectInfo);
                            if(row.getRecordNumber() == finalCount){
                                connectInfo.setClose(true);
                            }
                            logger.debug("已完成[{}]行数据,完成时间[{}]",row.getRecordNumber(),LocalDateTime.now());
                            data.clear();
                        }
                    }
            );
            if(data.size()>0){
                saveDb(sql,data,connectInfo);
                logger.debug("已完成[{}]行数据,完成时间[{}]",count,LocalDateTime.now());
            }
        } catch (IOException exception) {
            logger.error("读取csv文件出错:",exception.getMessage(),exception);
            throw DeanRuntimeException.throwException(996, exception.getMessage());
        }

    }

    /**
     * excel 文件导出 批量任务
     * @param connectInfo
     * @param filePath
     * @param sqls
     */
    public void csvExport(SysDbConnInfo connectInfo, String filePath, String [] sqls){
        // 文件目录若不存在 则直接新建
        File file = new File(filePath);
        if (!file.exists()) {
            file.mkdirs();
        }
        for (String sql : sqls) {
            String tmpName="";
            if (sql.lastIndexOf("###") > -1) {
                tmpName = sql.substring(sql.lastIndexOf("###") + 3);
                sql = sql.substring(0, sql.lastIndexOf("###"));
            }else {
                tmpName = UUID.randomUUID().toString();
            }
            csvExport(connectInfo,sql,filePath+ File.separator +tmpName+".csv",null);
        }
    }



    /**
     * excel 文件导出 批量任务
     * @param connectInfo
     * @param filePath
     * @param sqls
     */
    public void csvExport(SysDbConnInfo connectInfo, String filePath, String [] sqls,String delimiter){
        // 文件目录若不存在 则直接新建
        File file = new File(filePath);
        if (!file.exists()) {
            file.mkdirs();
        }
        for (String sql : sqls) {
            String tmpName="";
            if (sql.lastIndexOf("###") > -1) {
                tmpName = sql.substring(sql.lastIndexOf("###") + 3);
                sql = sql.substring(0, sql.lastIndexOf("###"));
            }else {
                tmpName = UUID.randomUUID().toString();
            }
            csvExport(connectInfo,sql,filePath+ File.separator +tmpName+".csv",delimiter);
        }
    }

    /**
     * excel 文件导出 单文件
     * @param connectInfo
     * @param sql
     * @param fileName
     */
    private void csvExport(SysDbConnInfo connectInfo, String sql, String fileName, String delimiter){
        // 校验sql
        if(StrUtil.isBlank(sql)){
            DeanRuntimeException.throwException(2001,"校验错误,参数[sql]不能为空");
        }
        if(!sql.startsWith(DeanUiConstants.START_SELECT_CHAR)){
            DeanRuntimeException.throwException(2005,"校验错误,不支持的sql操作");
        }
        // 如果文件名称为空,则自动生成文件名称
        if(StrUtil.isBlank(fileName)){
            DeanRuntimeException.throwException(2001,"校验错误,参数[fileName]不能为空");
        }
        // 生成 导出任务
        DeanExecutorService.producer("csv export task, sql:"+sql);
        GenCsvUtils genCsvUtils = new GenCsvUtils(connectInfo,sql,fileName,delimiter);
        DeanExecutorService.getInstance().execute(genCsvUtils);
    }

    /**
     * 保存结果到数据库
     * @param sql
     * @param datas
     * @param connInfo
     */
    private  void saveDb(String sql,List<List<String>> datas,SysDbConnInfo connInfo) {
        try{
            if (connection == null) {
                connInfo = DeanToolDao.getInstance().queryConnInfo(connInfo.getConnName());
                connection = DeanJdbcUtils.getConnection(connInfo);
                connection.setAutoCommit(false);
                ps = DeanJdbcUtils.getPreparedStatement(connection, sql);
            }
            for (List<String> data : datas) {
                int size = data.size();
                for (int i = 0; i < size; i++) {
                    ps.setString(i + 1, data.get(i));
                }
                ps.addBatch();
            }
            ps.executeBatch();
            ps.clearBatch();
            connection.commit();
            // DeanJdbcUtils.batchInsert(connInfo,sql,data);
        } catch (ClassNotFoundException e) {
            logger.error(e.getMessage(),e);
            DeanRuntimeException.throwException(996,e.getMessage());
        } catch (SQLException throwables) {
            logger.error(throwables.getMessage(),throwables);
            try {
                connInfo.setClose(true);
                connection.rollback();
            } catch (SQLException e) {
                logger.error(e.getMessage(),e);
            }
            DeanRuntimeException.throwException(996,throwables.getMessage());
        }finally {
            // 关闭连接
            if(connInfo.getClose()){
                DeanJdbcUtils.close(ps,connection);
                connection = null;
            }
        }

    }


    /**
     * csv 文件写入
     * @param fileName
     * @param delimiter
     * @param head
     * @param data
     */
    public void writeCsv(String fileName, String delimiter, String [] head, String[][] data){
        CSVFormat csvFormat = CSVFormat.RFC4180.builder().setDelimiter(delimiter).setQuoteMode(QuoteMode.NON_NUMERIC).setTrailingDelimiter(false).setHeader(head).build();
        try(CSVPrinter printer = csvFormat.print(Paths.get(fileName), Charset.forName("UTF-8"))) {
            printer.printRecords(data);
            printer.flush();
        } catch (IOException e) {
            logger.error("生成csv文件失败,失败原因:{}",e.getMessage(),e);
            throw DeanRuntimeException.throwException(996,e.getMessage());
        }
    }

    /**
     * csv 文件写入
     * 百万级别数据  100个字段测试
     * mysql 配置参考jdbc:mysql://192.168.100.12:3306/dean?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC&rewriteBatchedStatements=true&useCursorFetch=true
     * @param fileName
     * @param delimiter
     * @param sql
     */
    public void writeCsv(String fileName, String delimiter, String sql,String connName,Integer fetchSize){
        logger.debug("文件[{}]已开始导出,分割符[{}],sql[{}],connName[{}],fetchSize[{}],开始时间[{}]",fileName,delimiter,sql,connName,fetchSize, LocalDateTime.now());
        CSVFormat csvFormat = CSVFormat.RFC4180.builder().setDelimiter(delimiter).setQuoteMode(QuoteMode.MINIMAL).setTrailingDelimiter(false).build();
        SysDbConnInfo connInfo = DeanToolDao.getInstance().queryConnInfo(connName);
        List<List<String>> datas = new ArrayList<List<String>>();
        List<String> header = new ArrayList<String>();
        try(CSVPrinter printer = csvFormat.print(Paths.get(fileName), Charset.forName("UTF-8"));
            Connection connection = DeanJdbcUtils.getConnection(connInfo);) {
            PreparedStatement ps = connection.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY);
            ps.setFetchSize(fetchSize);
            ps.setFetchDirection(ResultSet.FETCH_REVERSE);
            ResultSet rs = ps.executeQuery();
            ResultSetMetaData resMetaData = rs.getMetaData();
            // 表头处理
            int columnCount = resMetaData.getColumnCount();
            for(int i = 0;i < columnCount; i++){
                header.add(resMetaData.getColumnName(i+1));
            }
            datas.add(header);
            int counter = 1;
            // 处理数据
            while (rs.next()){
                List<String> data = new ArrayList<String>();
                for (int i = 0; i < columnCount; i++) {
                    data.add(rs.getString(i+1));
                }
                datas.add(data);
                counter++;
                if(counter%fetchSize == 0){
                    printer.printRecords(datas);
                    printer.flush();
                    logger.debug("共写入[{}]条数据,完成时间[{}]",counter, LocalDateTime.now());
                    datas.clear();
                }
            }
            if(datas.size()>0){
                printer.printRecords(datas);
                printer.flush();
                logger.debug("共写入[{}]条数据,完成时间[{}]",counter, LocalDateTime.now());
            }
            logger.debug("文件[{}]已完成导出,完成时间[{}] bye bye ......",fileName, LocalDateTime.now());

        } catch (IOException e) {
            logger.error("生成csv文件失败,失败原因:{}",e.getMessage(),e);
            throw DeanRuntimeException.throwException(996,e.getMessage());
        } catch (SQLException throwables) {
            logger.error("生成csv文件失败,失败原因:{}",throwables.getMessage(),throwables);
            throw DeanRuntimeException.throwException(996,throwables.getMessage());
        } catch (ClassNotFoundException e) {
            logger.error("生成csv文件失败,失败原因:{}",e.getMessage(),e);
            throw DeanRuntimeException.throwException(996,e.getMessage());
        }
    }

//    public static void main(String[] args) {
//        ApacheCsvOprService.getInstance().writeCsv("/dean/test_db.csv",",","select * from test_csv_imp","dean",defaultFetchSize);
//
//       // ApacheCsvOprService.getInstance().writeCsv("/dean/test_db.csv",",","select * from test_csv_imp","dean",Integer.MIN_VALUE);
//    }


    private String crtSql(String tableName,String [] head){
        return DeanSqlUtils.createInsertSql(tableName,head);
    }

}
